package com.isyscore.os.metadata.database;

import com.alibaba.druid.util.JdbcConstants;
import com.isyscore.os.core.sqlcore.OracleBuilder;
import com.isyscore.os.metadata.enums.DataSourceTypeEnum;
import com.isyscore.os.metadata.model.dto.DataSourceDTO;
import com.isyscore.os.metadata.model.vo.ResultVO;
import org.apache.commons.lang3.StringUtils;

import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * @Description :
 * @Author: qkc
 * @Date: 2021/9/29 11:35
 */
public class OracleDatabase extends AbstractDatabase {

    public static final String ROW_ID = "ROW_ID";
    {
        dbType = DataSourceTypeEnum.ORACLE;
        //时间类型
        DATE_TYPE.add("DATE");
        DATE_TYPE.add("TIMESTAMP");

        //字段类型
        COLUMN_TYPE.put("VARCHAR2", "VARCHAR2(255)");
        COLUMN_TYPE.put("VARCHAR", "VARCHAR2(255)");
        COLUMN_TYPE.put("DECIMAL", "NUMBER(*,0)");
        COLUMN_TYPE.put("DOUBLE PRECISION", "FLOAT(126)");
        COLUMN_TYPE.put("FLOAT", "FLOAT(126)");
        COLUMN_TYPE.put("INT", "NUMBER(*,0)");
        COLUMN_TYPE.put("INTEGER", "NUMBER(*,0)");
        COLUMN_TYPE.put("LONG", "Long");
        COLUMN_TYPE.put("NUMBER", "NUMBER(*,0)");
        COLUMN_TYPE.put("SMALLINT", "NUMBER(*,0)");
        COLUMN_TYPE.put("CHAR", "CHAR(50)");
        COLUMN_TYPE.put("TIMESTAMP", "TIMESTAMP(6)");
        COLUMN_TYPE.put("DATE", "DATE");

        builder = new OracleBuilder();
    }

    private final String RENAME_TABLE = "ALTER TABLE %s rename to \"%s\"";

    private final String SELECT_TABLE_COL =
            "SELECT " +
                    "  '表备注' AS \"tableComment\", " +
                    "  COLUMN_NAME AS \"columnName\", " +
                    "  COLUMN_NAME AS \"oldName\", " +
                    "  COLUMN_NAME AS \"columnComment\", " +
                    "  DATA_TYPE AS \"dataType\", " +
                    "  DATA_TYPE || '(' || DATA_LENGTH || ')' AS \"columnType\", " +
                    "  TABLE_NAME AS \"tableName\" " +
                    "FROM " +
                    "  ALL_TAB_COLUMNS " +
                    "WHERE TABLE_NAME = '%s' " +
                    "  AND OWNER = '%s'";

    private final String SELECT_TABLE =
            "SELECT TABLE_NAME AS \"tableName\" FROM ALL_TABLES WHERE OWNER = '%s'";

    private final String SELECT_TABLE_COLUMN_LINK =
            "SELECT" +
                    "  '表备注' AS \"tableComment\"," +
                    "  COLUMN_NAME AS \"columnName\"," +
                    "  COLUMN_NAME AS \"oldName\"," +
                    "  COLUMN_NAME AS \"columnComment\"," +
                    "  DATA_TYPE AS \"dataType\"," +
                    "  DATA_TYPE || '(' || DATA_LENGTH || ')' AS \"columnType\"," +
                    "  TABLE_NAME AS \"tableName\" " +
                    "FROM" +
                    "  ALL_TAB_COLUMNS " +
                    "WHERE" +
                    "  TABLE_NAME IN ( SELECT TABLE_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME IN ( %s ) ) " +
                    "  AND OWNER = '%s'";

    private final String QUOTE = "\"";

    @Override
    public String escapeTbName(String originTableName, String databaseName) {
        return this.QUOTE + originTableName + QUOTE;
    }

    @Override
    public String escapeColName(String originColName) {
        return this.QUOTE + originColName + QUOTE;
    }

    @Override
    public String escapeColAliasName(String originAliasName) {
        return this.QUOTE + originAliasName + QUOTE;
    }

    @Override
    public String withOutEscapeColName(String escapeColName) {
        return escapeColName.replaceAll(QUOTE,"");
    }

    @Override
    public String withOutEscapeTableName(String escapeTableName) {
        return escapeTableName.replaceAll(QUOTE,"");
    }

    @Override
    public String getParseStr2DateEl(String dateVal) {
        return "to_date(" + dateVal + ",'yyyy-mm-dd hh24:mi:ss')";
    }

    @Override
    public String getParseDate2StrEl(String dateVal) {
        return "to_char(" + dateVal + ",'yyyy-mm-dd hh24:mi:ss')";
    }

    @Override
    public String getParseStr2IntEl(String dateVal) {
        return "TO_NUMBER("+dateVal+")";
    }

    @Override
    public void afterCreateStatement(Statement statement, DataSourceDTO dataSourceDTO) throws SQLException {
        statement.execute(String.format(
                "ALTER SESSION SET CURRENT_SCHEMA= \"%s\"",
                dataSourceDTO.getSelectDatabaseName()));
    }

    @Override
    public String jdbcUrl(String ip, int port, String dbName, String basicType, String basicValue) {
        String url = DataSourceTypeEnum.ORACLE.getUrl();
        if (StringUtils.isNotBlank(basicType)) {
            //oracle,需要判断basic下的 sid 还是 servicename
            int index = "SID".equalsIgnoreCase(basicType) ? 0 : 1;
            url = url.split(",")[index];
        }
        return String.format(url, ip, port, basicValue);
    }

    @Override
    public String tableStructSql(String tableName, String dbName) {
        return String.format(SELECT_TABLE_COL, tableName, dbName);
    }

    @Override
    public String tableListSql(String dbName) {
        return String.format(SELECT_TABLE, dbName);
    }

    @Override
    public String linkTableSql(String columns, String dbName) {
        return String.format(SELECT_TABLE_COLUMN_LINK, columns, dbName);
    }

    @Override
    public String renameTableSql(String oldTableName, String newTableName) {
        return String.format(RENAME_TABLE, oldTableName, newTableName);
    }

    @Override
    public String dropTableSql(String tableName) {
        String DROP_TABLE = "DROP TABLE %s";
        return String.format(DROP_TABLE, tableName);
    }

    @Override
    public String getQuote() {
        return QUOTE;
    }

    @Override
    public void wrapTableData(ResultVO resultVO) {
        //去掉oracle种的num列
        resultVO.setHead(resultVO.getHead().stream()
                .filter(col -> !Objects.equals(col.getColumnName(), ROW_ID))
                .collect(Collectors.toList()));

        List<Map<String, Object>> body = resultVO.getContent();
        for (Map<String, Object> map : body) {
            map.remove(ROW_ID);
        }

        for (Map<String, Object> map : resultVO.getContent()) {
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                Object val = entry.getValue();
                if (Objects.isNull(val)) {
                    continue;
                }
                if (!(val instanceof Number) && !(val instanceof String) && !(val instanceof Timestamp) && !(val instanceof Date) && !(val instanceof Time)) {
                    entry.setValue("[不支持的类型]");
                }
            }
        }
    }


}
